

/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 00 FOLDER SETTINGS

* clear
clear
clear 		mata
mat 		drop _all

* set path
global		path	""							

clear matrix
set maxvar 32767



* Load data
use "$path/0 Data/_Processed/Data_for_Panel_2020", clear

merge m:m entity yq using "$path/0 Data/_Processed/BHC_Bank_Ratios"
keep if inrange(yq,tq(2019q4),tq(2020q4))
drop _merge
bysort entity: replace gvkey = gvkey[_n-1] if missing(gvkey)
bysort entity: replace gvkey = gvkey[_n+1] if missing(gvkey)
drop if gvkey == .


** Adjust variables
gen 		unused_ci_assets 			= offbs_unused_loans	/ assets_total
gen 		unused_ci_tier1 			= offbs_unused_loans	/ reg_capital_tier1
replace 	reg_tier1_ratio 			= reg_tier1_ratio / 100
replace  	current_primary_dealer 		= 0 if  current_primary_dealer==.
replace 	derivatives_to_assets		= 0 if derivatives_to_assets == .


* Construct repayment variable (credit lines)
sort gvkey yq
tsset gvkey yq

gen perc_change_cl = 	(offbs_unused_loans - l.offbs_unused_loans) / l.offbs_unused_loans

by gvkey: gen repayment_callreport = (offbs_unused_loans-offbs_unused_loans[_n-1])/assets_total[_n-1]
by gvkey: gen drawdown_callreport = -repayment_callreport if inrange(yq,tq(2020q1),tq(2020q1))
by gvkey: replace drawdown_callreport = drawdown_callreport[_n-1] if missing(drawdown_callreport) 
gen exposure_2019q4 = offbs_unused_loans if inrange(yq,tq(2019q4),tq(2019q4))
by gvkey: replace exposure_2019q4 = exposure_2019q4[_n-1] if missing(exposure_2019q4)

* Non-linear effect, by quartiles
sum perc_change_cl, detail
xtile quintile_CL= perc_change_cl, n(4)
tab quintile_CL, gen(q)
tabstat perc_change_cl, stat(mean) by(quintile_CL)
gen repayment_1 =  perc_change_cl * q1
gen repayment_2 =  perc_change_cl * q2
gen repayment_3 =  perc_change_cl * q3
gen repayment_4 =  perc_change_cl * q4


	
	
** Remove Banks with missing inforamtion on key variables
egen missing = rowmiss(return beta liquidity_risk npl_loans nonintinc log_assets capital roa deposits_loans)

keep if missing == 0


** Label variables for output
label var liquidity_risk 			"Liquidity Risk"
label var unused_ci_assets 			"Unused C&I Loans / Assets"
label var unused_ci_tier1			"Unused C&I Loans / Tier-1"
label var liquidity_assets 			"Liquidity / Assets"
label var wholesale_assets 			"Wholesale Funding / Assets"
label var npl_loans 				"NPL / Loans"
label var capital 					"Equity Ratio"
label var nonintinc 				"Non-Interest Income"
label var non_interest				"Non-Interest Income"
label var log_assets 				"Log(Assets)"
label var roa 						"ROA"
label var deposits_loans 			"Deposits / Loans"
label var reg_tier1_ratio 			"Tier-1 Ratio"
label var income_diversity   		"Income Diversity"
label var log_Z  					"Distance-to-Default"
label var loans_assets 				"Loans / Assets"
label var deposits_assets  			"Deposits / Assets"
label var rmse_60 					"Idiosyncratic Volatility"
label var beta_real_estate 			"Real Estate Beta"
label var current_primary_dealer 	"Current Primary Dealer Indicator"
label var derivatives_to_assets 	"Derivatives / Assets"
label var beta						"Equity Beta"
label var perc_change_cl			"LC Repayment"
label var repayment_1				"LC Repayment (quartile 1)"
label var repayment_2				"LC Repayment (quartile 2)"
label var repayment_3				"LC Repayment (quartile 3)"
label var repayment_4				"LC Repayment (quartile 4)"




* Keep only banks in cross-section sample
*********************************************

merge 	m:m entity using "$path/0 Data/_Processed/sample.dta"
keep 	if _merge == 3
drop 	_merge

	
* Merge with recovery data Q2 - Q4 2020 (how much of the loss pre Fed intervention has been recovered?)
**************************************************************************************
	
*merge m:1 gvkey using "$path/0 Data/_Processed/temp_recovery"

merge m:1 gvkey using "$path/0 Data/_Processed/recovery"
keep if _merge == 3
drop _merge
sort gvkey date


* Merge with repayment data Q2 - Q4 2020 
**************************************************************************************

merge m:1 gvkey using "$path/0 Data/@@Sophie/June 2021/Processed/Panel.dta"

gen no_exp = (_merge == 1)
drop  _merge

foreach var of varlist share_* {
	
	replace `var' = 0 if `var' ==.
}

gen loss = -loss_23march20/mv_preCOVID


* merge with repayment risk Q2
*****************************
merge m:1 gvkey using "$path/0 Data/_Processed/bank_level_repayment_risk"
drop if _merge == 2
drop _merge

gen feestructure_j_scaled = feestructure_j/exposure_j
gen repay_j_scaled = repayment_j/exposure_j
gen repay_j_repayers_scaled = repayment_j_repayers/exposure_drawdown_j
gen repay_j_nonrepayers_scaled = -repayment_j_nonrepayers/exposure_j
gen repay_j_repayers_rw_scaled = repayment_j_repayers_rw/exposure_drawdown_j


foreach var of varlist fees_earned_* drawdown_j_* {
	replace `var' = `var'/exposure_j
}

foreach var of varlist drawdown_j* {
	gen `var'_scaled = `var'/exposure_j
}

gen repay_j_3B_scaled = repayment_j_3B/exposure_j
gen repay_j_allAs_scaled = repayment_j_allAs/exposure_j
gen repay_j_nonIG_scaled = repayment_j_nonIG/exposure_j
gen repay_j_NR_scaled = repayment_j_NR/exposure_j

gen repay_j_rep_3B_scaled = repayment_j_repayers_3B/exposure_j
gen repay_j_rep_allAs_scaled = repayment_j_repayers_allAs/exposure_j
gen repay_j_rep_nonIG_scaled = repayment_j_repayers_nonIG/exposure_j
gen repay_j_rep_NR_scaled = repayment_j_repayers_NR/exposure_j

gen repay_j_nonrep_3B_scaled = repayment_j_nonrepayers_3B/exposure_j
gen repay_j_nonrep_allAs_scaled = repayment_j_nonrepayers_allAs/exposure_j
gen repay_j_nonrep_nonIG_scaled = repayment_j_nonrepayers_nonIG/exposure_j
gen repay_j_nonrep_NR_scaled = repayment_j_nonrepayers_NR/exposure_j


** Label variables for output
label var fees_earned_j 			"Fees Earned"
label var repay_j_scaled 			"Repayments (+/-)"
label var repay_j_repayers_scaled	"Repayments (+)"
label var repay_j_nonrepayers_scaled "Additional Drawdowns (+)"
label var loss						"MV Loss Covid (+)"


* merge with repayment risk Q3
*****************************
merge m:1 gvkey using "$path/0 Data/_Processed/bank_level_repayment_risk_q3"
drop if _merge == 2
drop _merge

gen z_j_q3_scaled = z_j_q3/exposure_j
gen drawdown_j_q3_scaled = drawdown_j_q3/exposure_j
gen feestructure_j_q3_scaled = feestructure_j_q3/exposure_j
gen repay_j_q3_scaled = repayment_j_q3/exposure_j
gen repay_j_repayers_q3_scaled = repayment_j_repayers_q3/exposure_j
gen repay_j_nonrep_q3_scaled = repayment_j_nonrepayers_q3/exposure_j


gen repay_j_3B_q3_scaled = repayment_j_3B_q3/exposure_j
gen repay_j_allAs_q3_scaled = repayment_j_allAs_q3/exposure_j
gen repay_j_nonIG_q3_scaled = repayment_j_nonIG_q3/exposure_j
gen repay_j_NR_q3_scaled = repayment_j_NR_q3/exposure_j

gen repay_j_rep_3B_q3_scaled = repayment_j_repayers_3B_q3/exposure_j
gen repay_j_rep_allAs_q3_scaled = repayment_j_repayers_allAs_q3/exposure_j
gen repay_j_rep_nonIG_q3_scaled = repayment_j_repayers_nonIG_q3/exposure_j
gen repay_j_rep_NR_q3_scaled = repayment_j_repayers_NR_q3/exposure_j

gen repay_j_nonrep_3B_q3_scaled = repayment_j_nonrepayers_3B_q3/exposure_j
gen repay_j_nonrep_allAs_q3_scaled = repayment_j_nonrepayers_allAs_q3/exposure_j
gen repay_j_nonrep_nonIG_q3_scaled = repayment_j_nonrepayers_nonIG_q3/exposure_j
gen repay_j_nonrep_NR_q3_scaled = repayment_j_nonrepayers_NR_q3/exposure_j


* merge with repayment risk Q4
*****************************
merge m:1 gvkey using "$path/0 Data/_Processed/bank_level_repayment_risk_q4"
drop if _merge == 2
drop _merge

gen z_j_q4_scaled = z_j_q4/exposure_j
gen drawdown_j_q4_scaled = drawdown_j_q4/exposure_j
gen feestructure_j_q4_scaled = feestructure_j_q4/exposure_j
gen repay_j_q4_scaled = repayment_j_q4/exposure_j
gen repay_j_repayers_q4_scaled = repayment_j_repayers_q4/exposure_j
gen repay_j_nonrep_q4_scaled = repayment_j_nonrepayers_q4/exposure_j


gen repay_j_3B_q4_scaled = repayment_j_3B_q4/exposure_j
gen repay_j_allAs_q4_scaled = repayment_j_allAs_q4/exposure_j
gen repay_j_nonIG_q4_scaled = repayment_j_nonIG_q4/exposure_j
gen repay_j_NR_q4_scaled = repayment_j_NR_q4/exposure_j

gen repay_j_rep_3B_q4_scaled = repayment_j_repayers_3B_q4/exposure_j
gen repay_j_rep_allAs_q4_scaled = repayment_j_repayers_allAs_q4/exposure_j
gen repay_j_rep_nonIG_q4_scaled = repayment_j_repayers_nonIG_q4/exposure_j
gen repay_j_rep_NR_q4_scaled = repayment_j_repayers_NR_q4/exposure_j

gen repay_j_nonrep_3B_q4_scaled = repayment_j_nonrepayers_3B_q4/exposure_j
gen repay_j_nonrep_allAs_q4_scaled = repayment_j_nonrepayers_allAs_q4/exposure_j
gen repay_j_nonrep_nonIG_q4_scaled = repayment_j_nonrepayers_nonIG_q4/exposure_j
gen repay_j_nonrep_NR_q4_scaled = repayment_j_nonrepayers_NR_q4/exposure_j


* Generate capital buffer
*************************************************************************************

	sum capital if yq == 240, detail

	gen capital_buffer = capital - `r(p50)'
	label var capital_buffer			"Capital Buffer"
	
//////////////////////////////////////////////////////////////////////////////
////////// 2. Descriptive Statistics

* Table 4 Panel D 
tabstat return, s(n mean sd min max) by(yq)
	
	
//////////////////////////////////////////////////////////////////////////////
////////// 3. Stock returns in the post-COVID period
	
* Sort time-series	
sort gvkey yq
tsset gvkey yq	
	
	
* Full sample

eststo clear
	
* Table 4 Panel E
eststo, title("Model 1"): quietly reg return  l.liquidity_risk 	l.npl_loans l.capital  l.nonintinc l.log_assets l.roa l.deposits_loans  l.income_diversity  l.log_Z  l.loans_assets l.deposits_assets  l.rmse_60 l.beta_real_estate l.current_primary_dealer l.derivatives_to_assets l.beta, r cluster(gvkey)

eststo, title("Model 2"): quietly reg return  l.liquidity_risk 	l.npl_loans l.capital  l.nonintinc l.log_assets l.roa l.deposits_loans  l.income_diversity  l.log_Z  l.loans_assets l.deposits_assets  l.rmse_60 l.beta_real_estate l.current_primary_dealer l.derivatives_to_assets l.beta i.yq, r cluster(gvkey)

eststo, title("Model 3"): quietly reg return  l.unused_ci_assets 	l.liquidity_assets l.wholesale_assets 	l.npl_loans l.capital  l.nonintinc l.log_assets l.roa l.deposits_loans  l.income_diversity  l.log_Z  l.loans_assets l.deposits_assets  l.rmse_60 l.beta_real_estate l.current_primary_dealer l.derivatives_to_assets l.beta, r cluster(gvkey)

eststo, title("Model 4"): quietly reg return  l.unused_ci_assets 	l.liquidity_assets l.wholesale_assets 	l.npl_loans l.capital  l.nonintinc l.log_assets l.roa l.deposits_loans  l.income_diversity  l.log_Z  l.loans_assets l.deposits_assets  l.rmse_60 l.beta_real_estate l.current_primary_dealer l.derivatives_to_assets l.beta i.yq, r cluster(gvkey)

eststo, title("Model 5"): quietly reg return  l.liquidity_risk 	l.npl_loans l.capital  l.nonintinc l.log_assets l.roa l.deposits_loans  l.income_diversity  l.log_Z  l.loans_assets l.deposits_assets  l.rmse_60 l.beta_real_estate l.current_primary_dealer l.derivatives_to_assets l.beta if yq==241, r 

eststo, title("Model 6"): quietly reg return  l.liquidity_risk 	l.npl_loans l.capital  l.nonintinc l.log_assets l.roa l.deposits_loans  l.income_diversity  l.log_Z  l.loans_assets l.deposits_assets  l.rmse_60 l.beta_real_estate l.current_primary_dealer l.derivatives_to_assets l.beta if yq==242, r 

eststo, title("Model 7"): quietly reg return  l.liquidity_risk 	l.npl_loans l.capital  l.nonintinc l.log_assets l.roa l.deposits_loans  l.income_diversity  l.log_Z  l.loans_assets l.deposits_assets  l.rmse_60 l.beta_real_estate l.current_primary_dealer l.derivatives_to_assets l.beta if yq==243, r 


* Save results in xls
esttab using "$path/04 Results/Table 4 Panel E.csv", p(3) label star(* 0.10 ** 0.05 *** 0.01) stats(r2  N, labels(R-squared "Number obs.")) order(liquidity_risk) replace




* Table 7 A

sum repay_j_rep_3B_scaled repay_j_rep_allAs_scaled repay_j_rep_nonIG_scaled repay_j_rep_NR_scaled repay_j_rep_3B_q3_scaled repay_j_rep_allAs_q3_scaled repay_j_rep_nonIG_q3_scaled repay_j_rep_NR_q3_scaled

eststo clear

* Table 7 B
eststo, title("Model 1"): quietly reg return_q2 fees_earned_j repay_j_repayers_rw_scaled loss drawdown_callreport l.liquidity_risk  l.capital_buffer l.loans_assets l.rmse_60 l.current_primary_dealer l.derivatives_to_assets l.beta if yq==241, r

eststo, title("Model 1"): quietly reg return_q2 c.fees_earned_j##c.repay_j_repayers_rw_scaled loss drawdown_callreport l.liquidity_risk  l.capital_buffer l.loans_assets l.rmse_60 l.current_primary_dealer l.derivatives_to_assets l.beta if yq==241, r

eststo, title("Model 1"): quietly reg return_q2 c.fees_earned_j##c.repay_j_repayers_rw_scaled c.repay_j_repayers_rw_scaled##c.loss drawdown_callreport l.liquidity_risk  l.capital_buffer  l.loans_assets l.rmse_60 l.current_primary_dealer l.derivatives_to_assets l.beta if yq==241, r

eststo, title("Model 1"): quietly reg return_q2 c.fees_earned_j##c.repay_j_repayers_rw_scaled c.repay_j_repayers_rw_scaled##c.l.capital_buffer loss drawdown_callreport l.liquidity_risk  l.capital_buffer l.loans_assets l.rmse_60 l.current_primary_dealer l.derivatives_to_assets l.beta if yq==241, r

eststo, title("Model 1"): quietly reg return_q2 c.fees_earned_j##c.repay_j_repayers_rw_scaled c.repay_j_repayers_rw_scaled##c.drawdown_callreport loss drawdown_callreport l.liquidity_risk  l.capital_buffer l.loans_assets l.rmse_60 l.current_primary_dealer l.derivatives_to_assets l.beta if yq==241, r

eststo, title("Model 1"): quietly reg return_q2 c.fees_earned_j##c.repay_j_repayers_rw_scaled c.repay_j_repayers_rw_scaled##c.l.capital_buffer c.repay_j_repayers_rw_scaled##c.loss drawdown_callreport l.liquidity_risk  l.capital_buffer l.loans_assets l.rmse_60 l.current_primary_dealer l.derivatives_to_assets l.beta if yq==241, r



* Save results in xls
esttab using "$path/04 Results/Table 7 Panel B.csv", p(3) label star(* 0.10 ** 0.05 *** 0.01) stats(r2  N, labels(R-squared "Number obs.")) replace


* eof



